package com.cxtj;

import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.stereotype.Service;

import com.admin.dao.unicode;
import com.spring.task.ChainorcDao;

@Service("UserDAO")
public class UserDAO extends JdbcDaoSupport {
	
	@Resource
	public void setJb(JdbcTemplate jdbctemplate) {
		super.setJdbcTemplate(jdbctemplate);
	}	 
	public List<EtlBean> getETL(){
		String sql="select * from etl_table where status = 0";
		List<EtlBean> cxList=this.getJdbcTemplate().query(sql.toString(),new Object[] {}, new BeanPropertyRowMapper<EtlBean> (EtlBean.class));	
		return cxList;		
	}
	
	public HashMap<String,Object> selectEtlPage(String page){		
		int pageNo=10;
		StringBuffer sql=new StringBuffer("select * from etl_table where status = 0 ");
		HashMap<String,Object> returnMap=new HashMap<String,Object>();		
		int total=getRecord();   //bug  to do
		if(page!=null && !"".equals(page) && Integer.valueOf(page)>1)
		{	int pages=Integer.valueOf(page);			
	    	if(((pages-1)*pageNo)>total)    //判断页码是否越界
	    		{
	    			page=String.valueOf(total/pageNo<1?0:(total/pageNo)-1);    //若越界则计算并取最大页
	    		}	    	
		}
		if(page!=null && !"".equals(page))
		{
			sql.append(" limit "+(Integer.valueOf(page)-1)*pageNo+", "+pageNo);			
		}else{
			sql.append(" limit 0,10");
		}	

		System.out.println(sql);
		List<EtlBean> cxList=this.getJdbcTemplate().query(sql.toString(),new Object[] {}, new BeanPropertyRowMapper<EtlBean> (EtlBean.class));			
		if(cxList!=null)
		{	
			returnMap.put("datas", cxList);
			returnMap.put("num", total);		
			}		
		return returnMap;	
	}
	
	
	public boolean deleteETL(String id){
		String sql;
		if(id!=null && !"".equals(id)){		
			sql="update etl_table set status=1 where status=0  and id in( "+id+" )";			
		}else{
			return false;
		}		
		System.out.println("SQL:"+sql);
		int res = this.getJdbcTemplate().update(sql);
		if(res>0){
			return true;
		}else{
		return false;
		}
	}
	@Resource(name = "ChainorcDao")
	  ChainorcDao coDao;
	public HashMap<String,Object> insertETL(String tablename,String cnName,String username){
		HashMap<String,Object> returnMP=new HashMap<String,Object>();
		if(tablename == null || "".equals(tablename) || username == null || "".equals(username) ){
			returnMP.put("status", 1);
			returnMP.put("message", "表名或用户名不能为空");
			return returnMP;
		}
		String cntSQL ="select count(1) from etl_table where status = 0 AND table_name = '"+tablename+"' and user_name='"+username+"' ";
		int cnt = this.getJdbcTemplate().queryForInt(cntSQL);
		String sql;
		if(cnt>0){
			returnMP.put("status", 1);
			returnMP.put("message", "该表名已配置");
			return returnMP;
		}
		int oracnt = coDao.countRecord(tablename, username);
		if(oracnt>0){
			returnMP.put("status", 1);
			returnMP.put("message", "该表名不存在");
			return returnMP;
		}
		if(cnName!=null && !"".equals(cnName))
		{
		 sql="insert into etl_table(table_name,cn_name,user_name) values('"+tablename+"','"+cnName+"','"+username+"') ";
		}
		else{
			sql="insert into etl_table(table_name,user_name) values('"+tablename+"','"+username+"') ";
		}
		int res = this.getJdbcTemplate().update(sql);
		if(res==1)	{
			returnMP.put("status", 0);
			returnMP.put("message", "添加成功");
			return returnMP;
		}
		else{ 
			returnMP.put("status", 1);
			returnMP.put("message", "重复添加");
			return returnMP;			
		
		}
	}
	
	private  int getRecord() {				
		String sql="select count(1) from etl_table where status = 0 ";
		return this.getJdbcTemplate().queryForInt(sql);		
	}
	
	public  List<EtlBean> getEtlRec() {				
		String sql="select * from etl_table where status = 0 order by table_name desc";
		return this.getJdbcTemplate().query(sql.toString(),new Object[] {}, new BeanPropertyRowMapper<EtlBean> (EtlBean.class));	
		
	}
	
	public boolean updateETL(EtlBean inETL){
		StringBuffer sql=new StringBuffer("update etl_table set ");
		if(inETL!=null && !"".equals(inETL.getTable_name())){	
			SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");   
			Timestamp ts= new Timestamp(System.currentTimeMillis());			
			sql.append(" etl_time= '"+ts.toString()+"' ");			
			if(inETL.getData_time()!=null && !"".equals(inETL.getData_time())){
				sql.append(",data_time='"+inETL.getData_time()+"'  ");   //
			}
			if(inETL.getData_count()>0){
				sql.append(" ,data_count="+inETL.getData_count()+"");
			}
			if(inETL.getUser_name()!=null && !"".equals(inETL.getUser_name())){
				sql.append(" ,user_name = '"+inETL.getUser_name()+"' ");
			}
			sql.append(" where table_name = '"+inETL.getTable_name()+"' ");
			
			if(inETL.getUser_name()!=null && !"".equals(inETL.getUser_name())){
				sql.append(" and user_name = '"+inETL.getUser_name()+"' ");
			}
		}else{
			return false;
		}		
		System.out.println(this.getClass()+":"+sql);
		int res = this.getJdbcTemplate().update(sql.toString());
		if(res>0){
			return true;
		}else{
		return false;
		}
	}
	
}
